package com.budwk.app.sqfw.utils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.config.AppException;
import com.budwk.app.sqfw.dto.onlineExcel.SqfwExcelOnlineAllDTO;
import com.budwk.app.sqfw.dto.onlineExcel.SqfwFormSummaryOnlineDTO;
import com.budwk.app.sqfw.enums.datamange.TypeToPKeyEnum;
import com.budwk.app.sqfw.models.Sqfw_datamange_config;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.datamange.DataMangeConfigService;
import com.budwk.app.sqfw.vo.dapingvo.SqfwDataStatisticStreetForAdminVO;
import com.budwk.app.sqfwV2.dto.entity.SqfwShopsBaseInfo;
import com.budwk.app.sqfwV2.service.ShopsBaseInfoService;
import com.budwk.app.util.ThreadPoolExecutorUtil;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.result.Result;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.security.utils.SecurityUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Chain;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.lang.random.R;
import org.nutz.trans.Trans;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ConcurrentSkipListMap;
import java.util.concurrent.ThreadPoolExecutor;

/**
 * @program: community-admin
 * @description: 社区巡查数据清洗工具类
 * @author: LJT
 * @create: 2024-06-08 11:50
 **/
@Slf4j
public class CommunityDataCleanUtils {

    public static final String COUNTY_MAIN_TABLE = "shequ.\"sqfw_datamange_community\""; // 街道级大通表
    public static final String DISTRICT_MAIN_TABLE = "shequ.\"sqfw_datamange_district\"";// 区县级大通表
    public static final String CITY_MAIN_TABLE = "shequ.\"sqfw_datamange_city\"";    // 市级大通表
    public static final String PROVINCE_MAIN_TABLE = "shequ.\"sqfw_datamange_province\"";// 省级大通表

    // 燃气值转换map
    private static Map<String, String> gasMap = new HashMap<>();

    static {
        gasMap.put("fireAndElectricityUsage1", "液化石油气");
        gasMap.put("fireAndElectricityUsage2", "城镇燃气用二甲醚");
        gasMap.put("useGasCylinders1", "普通瓶（普通瓶俗称“黑气瓶”，也称报废瓶）");
        gasMap.put("useGasCylinders2", "共享瓶");
        gasMap.put("gasCylinderCirculation1", "正常");
        gasMap.put("gasCylinderCirculation2", "异常");
        gasMap.put("gasCylinderCirculation3", "无信息");
        gasMap.put("connectingHoses1", "正常");
        gasMap.put("connectingHoses2", "长度小于0.5m或大于2m)");
        gasMap.put("connectingHoses3", "三通连接（橡胶软管禁止）");
        gasMap.put("connectingHoses4", "龟(断)裂");
        gasMap.put("connectingHoses5", "其它：备注说明");
        gasMap.put("combustibleGasAlarm1", "已安装并正常使用");
        gasMap.put("combustibleGasAlarm2", "已安装未正常使用");
        gasMap.put("combustibleGasAlarm3", "未安装");
    }

    // 儿童探访值转换map
    private static Map<String, String> childVisitMap = new HashMap<>();

    static {
        childVisitMap.put("children_category_raising", "事实无人抚养儿童");
        childVisitMap.put("children_category_behind", "留守儿童");
        childVisitMap.put("children_category_difficult", "困难儿童");
        childVisitMap.put("children_category_other", "其他");
        childVisitMap.put("children_category_orphan", "孤儿");
        childVisitMap.put("work_situation_in_school", "在读");
        childVisitMap.put("work_situation_no_school", "非在读");
        childVisitMap.put("physical_hearing", "听力残疾");
        childVisitMap.put("physical_language", "语言残疾");
        childVisitMap.put("physical_health", "健康");
        childVisitMap.put("physical_other", "其他");
        childVisitMap.put("physical_physical_disability", "肢体残疾");
        childVisitMap.put("physical_disability", "智力残疾");
        childVisitMap.put("physical_visual", "视力残疾");
        childVisitMap.put("physical_serious_illness", "重病");
    }

    /**
     * 查询数据字典字段表，获取excel的key信息
     * 根据类型洗数据用此方法
     *
     * @return
     */
    public static LinkedHashMap<String, String> queryDictFieldInfoByAll() {
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        StringBuffer sb = new StringBuffer("SELECT field_name, field_code FROM shequ.\"sqfw_template_field\" stc;");
        Sql sql = Sqls.create(sb.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                Map<String, String> map = new LinkedHashMap<String, String>();
                map.put("placeName", "场所名称");
                map.put("type", "类型");
                map.put("placeType", "场所类型");
                map.put("socialCreditCode", "统一社会信用代码");
                map.put("siteManager", "现场管理人员");
                map.put("operator", "负责人");
                map.put("name", "姓名");
                map.put("visitedName", "被探访人姓名");
                map.put("nameOfTheInterviewee", "被探访人姓名");
                map.put("operatorPhone", "联系电话");
                map.put("operatorTelephone", "联系电话");
                map.put("iDNumber", "身份证号");
                map.put("idcard", "身份证号");
                map.put("birthday", "出生日期");
                map.put("dateOfBirth", "出生日期");
                map.put("operatOrganName", "运营机构名称");
                map.put("operatOraganPhone", "运营机构联系电话");
                map.put("operatOraganHead", "运营机构负责人");
                map.put("address", "地址");
                map.put("nation", "民族");
                map.put("gender", "性别");
                map.put("phone", "联系电话");
                map.put("patrolType", "巡逻类型");
                map.put("siteManagerName", "现场管理人员");
                map.put("siteManagerPhone", "现场管理人员联系电话");
                map.put("careCategory", "关爱类别");
                map.put("familyDifficulties", "家庭是否困难");
                String codes = "placeName,type,placeType,socialCreditCode,siteManager,operatorPhone,operatorTelephone," +
                        "name,idcard,birthday,nation,gender,phone,operatOrganName,operatOraganPhone,operatOraganHead,address," +
                        "iDNumber,patrolType,siteManagerName,siteManagerPhone,careCategory,familyDifficulties,operator,visitedName," +
                        "nameOfTheInterviewee,dateOfBirth";
                while (rs.next()) {
                    for (int i = 0; i < columnCount; i++) {
                        String fieldCode = rs.getString("field_code");
                        if (ObjectUtils.isNotEmpty(map.get(fieldCode)) || codes.contains(fieldCode)) {
                            continue;
                        }
                        String fieldName = rs.getString("field_name");
                        map.put(fieldCode, fieldName);
                    }
                }

                return map;
            }
        });
        dao.execute(sql);
        return sql.getObject(LinkedHashMap.class);
    }

    /**
     * 查询数据字典配置表，根据type类型清洗数据，获取excel的key信息
     *
     * @return
     */
    public static String queryDictFieldInfoByType(String type) {
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        StringBuffer sb = new StringBuffer("SELECT config_json FROM shequ.\"sqfw_template_config\" stc WHERE config_name like @type ORDER BY create_time DESC LIMIT 1");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("type", "%" + type + "%");
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                String result = "";
                while (rs.next()) {
                    for (int i = 0; i < columnCount; i++) {
                        result = rs.getString("config_json");
                    }
                }
                return result;
            }
        });
        dao.execute(sql);
        return sql.getString();
    }

    /**
     * 查询数据字典配置表，根据type类型清洗数据，获取excel的key信息
     * 新位置
     *
     * @return
     */
    public static String queryDynamicFieldInfoByType(String type) {
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        StringBuffer sb = new StringBuffer("SELECT conf FROM shequ.\"sqfw_dynamic_form_conf\" stc WHERE type like @type LIMIT 1");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("type", "%" + type + "%");
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                String result = "";
                while (rs.next()) {
                    for (int i = 0; i < columnCount; i++) {
                        result = rs.getString("conf");
                    }
                }
                return result;
            }
        });
        dao.execute(sql);
        return sql.getString();
    }

    /**
     * 递归解析解析数据字典配置json
     * 来自：sqfw_template_config表 config_json字段
     *
     * @param configJson
     * @param map
     */
    public static void parseDictFeild(String configJson, LinkedHashMap<String, String> map) {
        ConcurrentSkipListMap<String, String> resultMap = new ConcurrentSkipListMap<String, String>();
        JSONArray jsonArray = JSONUtil.parseArray(configJson);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            String dictCode = jsonObject.getString("dictCode");
            String visDictName = jsonObject.getString("visDictName");
            map.put(dictCode, visDictName);
            // 有子数据，递归
            String children = jsonObject.getString("children");
            JSONArray jsonArrayChildren = JSONUtil.parseArray(children);
            if (StringUtils.isNotEmpty(children) && jsonArrayChildren.size() > 0) {
                parseDictFeild(children, map);
            }
        }
    }

    /**
     * 来自：sqfw_dynamic_form_conf conf字段
     *
     * @param configJson
     * @param map
     */
    public static void parseDictFeildNew(String configJson, LinkedHashMap<String, String> map) {
        if (!JSONUtil.isTypeJSON(configJson)) {
            return;
        }
        JSONObject jsonObject = JSONObject.parseObject(configJson);
        String properties = jsonObject.getString("properties");
        if (null == properties) {
            return;
        }
        JSONObject propertiesJson = JSONObject.parseObject(properties);
        String children = propertiesJson.getString("children");
        if (null == children) {
            return;
        }
        JSONArray jsonArray = JSONUtil.parseArray(children);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObjectSub = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            String dictCode = jsonObjectSub.getString("key");
            String visDictName = jsonObjectSub.getString("name");
            map.put(dictCode, visDictName);
        }
    }

    /**
     * 解析数据到Excel json
     */
    public static SqfwExcelOnlineAllDTO parseSummaryData(ConcurrentSkipListMap.Entry<String, String> entry, String updateDate) {
        SqfwExcelOnlineAllDTO sqfwExcelOnlineAllDTO = new SqfwExcelOnlineAllDTO();
        sqfwExcelOnlineAllDTO.setId(R.UU32());
        sqfwExcelOnlineAllDTO.setField(entry.getKey());
        String value = entry.getValue();
        if (JSONUtil.isTypeJSON(value)) {
            JSONObject jsonObject = null;
            try {
                jsonObject = JSONObject.parseObject(value);
                for (String key : jsonObject.keySet()) {
                    value = String.valueOf(jsonObject.get(key));
                }
            } catch (Exception ex) {
                log.error("json解析异常：{}", value);
            }
        }
        sqfwExcelOnlineAllDTO.setV(isTimeStamp(value));
        sqfwExcelOnlineAllDTO.setT(NumberUtil.isNumber(value) ? "i" : "s");
        sqfwExcelOnlineAllDTO.setD(updateDate);
        return sqfwExcelOnlineAllDTO;
    }

    /**
     * 解析param_data数据到map
     */
    public static void parseSummaryParamData(String jsonData, HashMap<String, String> map) {
        if (JSONUtil.isTypeJSONObject(jsonData)) {
            // 如果是json对象
            JSONObject jsonObject = JSONObject.parseObject(jsonData);
            for (String key : jsonObject.keySet()) {
                String value = String.valueOf(jsonObject.get(key));
                if (JSONUtil.isTypeJSONObject(value) || JSONUtil.isTypeJSONArray(value)) {
                    try {
                        if ("briefList".equals(key)) {
                            // 如果是json数组
                            JSONArray jsonArray = JSONUtil.parseArray(value);
                            for (int i = 0; i < jsonArray.size(); i++) {
                                JSONObject subJson = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
                                String subKey = subJson.getString("key");
                                String subValue = subJson.getString("whetherOrNot");
                                String subProblem = subJson.getString("problemContent");
                                map.put(subKey, subValue);
                                if ("familyDifficulties".equals(subKey)) {
                                    map.put("familyDifficultiesSituation", subProblem);
                                } else if ("needHelp".equals(subKey)) {
                                    map.put("needHelpSituation", subProblem);
                                }
                            }
                        }
                    } catch (Exception ex) {
                        log.error("异常数据1：{}", value);
                        continue;
                    }
                    parseSummaryParamData(value, map);
                }
                if (!JSONUtil.isTypeJSON(value) && StringUtils.isNotEmpty(value)) {
                    if (!map.containsKey(key) || "[]".equals(key) || "{}".equals(key)) {
                        continue;
                    }
                    value = value.replaceAll(" ", "");
                    value = "true".equalsIgnoreCase(value) ? "是" : value;
                    value = "false".equalsIgnoreCase(value) ? "否" : value;
                    map.put(key, value);
                } else if (JSONUtil.isTypeJSON(value)) {
                    parseSummaryParamData(value, map);
                }
            }
        } else if (JSONUtil.isTypeJSONArray(jsonData)) {
            // 如果是json数组
            JSONArray jsonArray = JSONUtil.parseArray(jsonData);
            for (int i = 0; i < jsonArray.size(); i++) {
                String itemJsonTmp = String.valueOf(jsonArray.get(i));
                if ("[]".equals(itemJsonTmp) || "{}".equals(itemJsonTmp)) {
                    continue;
                }
                JSONObject jsonObject = null;
                try {
                    jsonObject = JSONObject.parseObject(itemJsonTmp);
                } catch (Exception ex) {
                    // 有类似 ["8"] 的数据，导致解析异常
                    log.error("异常数据：{}", itemJsonTmp);
                    continue;
                }
                if (null == jsonObject) {
                    continue;
                }
                if (jsonObject.containsKey("fields")) {
                    String fields = String.valueOf(jsonObject.get("fields"));
                    if (!map.containsKey(fields)) {
                        continue;
                    }
                    String key = String.valueOf(jsonObject.get("key")).replaceAll(" ", "");
                    key = "true".equalsIgnoreCase(key) ? "是" : key;
                    key = "false".equalsIgnoreCase(key) ? "否" : key;
                    String riskDesc = String.valueOf(jsonObject.get("riskDesc"));
                    LinkedHashMap<String, String> valueMap = new LinkedHashMap<String, String>();
                    valueMap.put(riskDesc, key);
                    map.put(fields, JSONUtil.toJsonStr(valueMap));
                    continue;
                }
                for (String key : jsonObject.keySet()) {
                    if (!map.containsKey(key)) {
                        continue;
                    }
                    String value = String.valueOf(jsonObject.get(key));
                    if (!JSONUtil.isTypeJSON(value) && StringUtils.isNotEmpty(value)) {
                        value = value.replaceAll(" ", "");
                        value = "true".equalsIgnoreCase(value) ? "是" : value;
                        value = "false".equalsIgnoreCase(value) ? "否" : value;
                        map.put(key, value);
                    } else if (JSONUtil.isTypeJSON(value)) {
                        parseSummaryParamData(value, map);
                    }
                }
            }
        }
    }

    /**
     * 将param_data数据字段值处理成Map
     */
    public static void cleanParamData(List<Sqfw_formSummary_new> sqfwFormSummaryList, List<SqfwFormSummaryOnlineDTO> onlineList, LinkedHashMap<String, String> fieldMap) {
        ThreadPoolExecutor threadPoolExecutor = ThreadPoolExecutorUtil.getInstance();
        CompletableFuture[] cf = sqfwFormSummaryList.stream().map(item -> CompletableFuture.runAsync(() ->
                        processParamData(item, onlineList, fieldMap), threadPoolExecutor)
                .thenApplyAsync(r -> r)).toArray(CompletableFuture[]::new);
        CompletableFuture.allOf(cf).join();
    }

    /**
     * 处理param_data源数据
     *
     * @param sqfwFormSummaryNew
     * @param onlineList
     * @param fieldMap
     */
    private static void processParamData(Sqfw_formSummary_new sqfwFormSummaryNew, List<SqfwFormSummaryOnlineDTO> onlineList, LinkedHashMap<String, String> fieldMap) {
        SqfwFormSummaryOnlineDTO subOnlineDTO = new SqfwFormSummaryOnlineDTO();
        BeanUtil.copyProperties(sqfwFormSummaryNew, subOnlineDTO);
        subOnlineDTO.setSqfwSummaryId(sqfwFormSummaryNew.getId());
        subOnlineDTO.setParamData(null);
        // 解析param_data数据
        LinkedHashMap<String, String> paramDataMap = new LinkedHashMap<String, String>();
        paramDataMap.putAll(fieldMap);
        for (String key : paramDataMap.keySet()) {
            paramDataMap.put(key, "");
        }
        sqfwFormSummaryNew.setParamData(sqfwFormSummaryNew.getParamData().replaceAll(" ", "")
                .replaceAll(" ", ""));
        CommunityDataCleanUtils.parseSummaryParamData(sqfwFormSummaryNew.getParamData(), paramDataMap);
        // 单独处理经纬度和商铺信息
        if (StringUtils.isEmpty(paramDataMap.get("longitude"))) {
            paramDataMap.put("longitude", sqfwFormSummaryNew.getLongitude());
        }
        if (StringUtils.isEmpty(paramDataMap.get("latitude"))) {
            paramDataMap.put("latitude", sqfwFormSummaryNew.getLatitude());
        }
        if (StringUtils.isEmpty(paramDataMap.get("socialCreditCode")) && StringUtils.isNotEmpty(sqfwFormSummaryNew.getShopsBaseInfoId())) {
            ShopsBaseInfoService shopsBaseInfoService = (ShopsBaseInfoService) NutzIocUtils.getBeanByType(ShopsBaseInfoService.class);
            SqfwShopsBaseInfo sqfwShopsBaseInfo = shopsBaseInfoService.fetch(Cnd.where(SqfwShopsBaseInfo::getId, "=", sqfwFormSummaryNew.getShopsBaseInfoId()));
            if (null != sqfwShopsBaseInfo) {
                paramDataMap.put("socialCreditCode", sqfwShopsBaseInfo.getSocialCreditCode());
            }
        }
        // 燃气相关数据转换
        if (StringUtils.isNotEmpty(paramDataMap.get("fireAndElectricityUsage"))) {
            String sourceValue = paramDataMap.get("fireAndElectricityUsage");
            String nowValue = gasMap.get("fireAndElectricityUsage" + sourceValue);
            paramDataMap.put("fireAndElectricityUsage", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
        }
        if (StringUtils.isNotEmpty(paramDataMap.get("useGasCylinders"))) {
            String sourceValue = paramDataMap.get("useGasCylinders");
            String nowValue = gasMap.get("useGasCylinders" + sourceValue);
            paramDataMap.put("useGasCylinders", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
        }
        if (StringUtils.isNotEmpty(paramDataMap.get("gasCylinderCirculation"))) {
            String sourceValue = paramDataMap.get("gasCylinderCirculation");
            String nowValue = gasMap.get("gasCylinderCirculation" + sourceValue);
            paramDataMap.put("gasCylinderCirculation", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
        }
        if (StringUtils.isNotEmpty(paramDataMap.get("connectingHoses"))) {
            String sourceValue = paramDataMap.get("connectingHoses");
            String nowValue = gasMap.get("connectingHoses" + sourceValue);
            paramDataMap.put("connectingHoses", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
        }
        if (StringUtils.isNotEmpty(paramDataMap.get("combustibleGasAlarm"))) {
            String sourceValue = paramDataMap.get("combustibleGasAlarm");
            String nowValue = gasMap.get("combustibleGasAlarm" + sourceValue);
            paramDataMap.put("combustibleGasAlarm", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
        }
        // 儿童探访相关数据转换
        if (StringUtils.isNotEmpty(subOnlineDTO.getType()) && TypeToPKeyEnum.CHILD_VISIT_RECORD.getType().equals(subOnlineDTO.getType())) {
            if (StringUtils.isNotEmpty(paramDataMap.get("childrenType"))) {
                String sourceValue = paramDataMap.get("childrenType");
                String nowValue = childVisitMap.get(sourceValue);
                paramDataMap.put("childrenType", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
            }
            if (StringUtils.isNotEmpty(paramDataMap.get("learningCondition"))) {
                String sourceValue = paramDataMap.get("learningCondition");
                String nowValue = childVisitMap.get(sourceValue);
                paramDataMap.put("learningCondition", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
            }
            if (StringUtils.isNotEmpty(paramDataMap.get("physicalCondition"))) {
                String sourceValue = paramDataMap.get("physicalCondition");
                String nowValue = childVisitMap.get(sourceValue);
                paramDataMap.put("physicalCondition", StringUtils.isEmpty(nowValue) ? sourceValue : nowValue);
            }
        }
        // 林河汛灾防治的数据转换
        if (StringUtils.isNotEmpty(subOnlineDTO.getType()) && TypeToPKeyEnum.LINHE_PROP_CURE.getType().equals(subOnlineDTO.getType())) {
            paramDataMap.put("officerName", paramDataMap.get("name"));
            String peers = paramDataMap.get("peers");
            if (StringUtils.isNotEmpty(peers)) {
                List<String> ids = Arrays.asList(peers.split(","));
                paramDataMap.put("peers", queryUserNameByIds(ids));
            }
        }
        subOnlineDTO.setExcelDataMap(paramDataMap);
        subOnlineDTO.setExcelSeg("body");
        onlineList.add(subOnlineDTO);
    }

    /**
     * 格式化时间字符串
     */
    public static String formatDate(String timeStr) {
        String dateString = timeStr;
        SimpleDateFormat originalFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.ENGLISH);
        SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date date = originalFormat.parse(dateString);
            dateString = targetFormat.format(date);
        } catch (ParseException e) {
            log.error(ResultCode.TIMESTAMP_ERROR.getMsg());
        }
        return dateString;
    }

    /**
     * 检查是否是时间戳
     *
     * @param str
     * @return
     */
    private static String isTimeStamp(String str) {
        boolean flag = false;
        try {
            Long.parseLong(str);
            if (str.length() == 10 || str.length() == 13) {
                // 10位或13位时间戳通常是10位，13位是毫秒
                flag = true;
            }
        } catch (NumberFormatException e) {
            // 不是数字，不是时间戳
            flag = false;
        }
        String nowdate = str;
        if (flag) {
            nowdate = DateUtil.formatDateTime(new Date(Long.parseLong(str)));
        }
        return nowdate;
    }

    /**
     * 获取该类型对应最大行号
     */
    public static Integer getMaxNumber(String type) {
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        Sql sql = Sqls.create("SELECT MAX(CAST(row_num AS int)) from shequ.\"sqfw_form_summary_online\" WHERE type = @type");
        sql.setParam("type", type);
        sql.setCallback(Sqls.callback.integer());
        dao.execute(sql);
        return sql.getInt() + 1;
    }

    /**
     * 解析审核上报数据，为插入数据做准备
     */
    public static String parseExcelJsonForMainTable(String excelJson, Map<String, Map<String, String>> resultMap) {
        if (!JSONUtil.isTypeJSON(excelJson)) {
            log.info("解析内容异常：{}", excelJson);
            return "";
        }
        JSONArray jsonArray = JSONUtil.parseArray(excelJson);
        StringBuffer sb = new StringBuffer();
        int indexNum = 0;// json下标位置
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            String field = jsonObject.getString("field");
            if ("field".contains("_time")) {
                // 过滤掉时间单元格
                continue;
            }
            sb.append(String.valueOf(jsonArray.get(i))).append(",");

            // 填充值，后面做更新使用
            String value = jsonObject.getString("v");
            String summaryId = jsonObject.getString("parentId");
            String customKey = summaryId + "_" + field;
            Map<String, String> subMap = new HashMap<String, String>();
            // 存放单元格，后续方便新增
            subMap.put(customKey + "_cell", jsonObject.toJSONString());
            // 单元格值
            subMap.put(customKey + "_value", jsonObject.getString("v"));
            // 单元格Id
            subMap.put(customKey + "_id", jsonObject.getString("id"));
            // 单元格更新时间
            subMap.put(customKey + "_d", jsonObject.getString("d"));
            resultMap.put(customKey, subMap);
            indexNum++;
        }
        return "[" + sb.substring(0, sb.length() - 1) + "]";
    }

    /**
     * 解析审核上报数据，为插入数据做准备
     */
    public static String parseExcelJsonForMainTableNew(String summaryId, String excelJsonSource, String excelJson, Map<String, String> updateResultMap, StringBuffer addResultStr) {
        if (!JSONUtil.isTypeJSON(excelJson)) {
            log.info("解析内容异常：{}", excelJson);
            return "";
        }
        JSONArray jsonArray = JSONUtil.parseArray(excelJson);
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            String field = jsonObject.getString("field");
            if ("field".contains("_time")) {
                // 过滤掉时间单元格
                continue;
            }
            // 去掉空值
            String value = jsonObject.getString("v");
            if (StringUtils.isEmpty(value)) {
                continue;
            }
            sb.append(String.valueOf(jsonArray.get(i))).append(",");
            // 源数据不为空才去检查更新或新增
            if (StringUtils.isNotEmpty(excelJsonSource)) {
                // 填充值，后面做更新使用
                String customKey = summaryId + "_" + field;
                Map<String, String> subMap = new HashMap<String, String>();
                // 存放单元格，后续方便新增
                if (excelJsonSource.contains("\"" + field + "\"")) {
                    updateResultMap.put(customKey + "_cell", jsonObject.toJSONString());
                } else {
                    addResultStr.append("," + jsonObject.toJSONString());
                }
            }
        }
        return "[" + sb.substring(0, sb.length() - 1) + "]";
    }

    /**
     * 解析审核上报数据，为插入数据做准备
     */
    public static String parseExcelJsonEachUpdate(String summaryId, String excelJson, Map<String, String> updateResultMap) {
        if (!JSONUtil.isTypeJSON(excelJson)) {
            log.info("解析内容异常：{}", excelJson);
            return "";
        }
        JSONArray jsonArray = JSONUtil.parseArray(excelJson);
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            String field = jsonObject.getString("field");
            // 填充值，后面做更新使用
            String customKey = summaryId + "_" + field;
            Map<String, String> subMap = new HashMap<String, String>();
            // 存放单元格，后续方便新增
            String updateResultMapStr = updateResultMap.get(customKey + "_cell");
            if (StringUtils.isNotEmpty(updateResultMapStr)) {
                sb.append(updateResultMapStr).append(",");
            } else {
                sb.append(String.valueOf(jsonArray.get(i))).append(",");
            }
        }
        return "[" + sb.substring(0, sb.length() - 1) + "]";
    }

    /**
     * 过滤掉时间单元格
     * [[{_time},{}],[{_time},{}]]
     */
    public static String filtJsonArraySubData(String configJson, String filtStr) {
        if (!JSONUtil.isTypeJSON(configJson) || !JSONUtil.isTypeJSONArray(configJson)) {
            throw new AppException("==========过滤json格式异常!==========");
        }
        StringBuffer strAll = new StringBuffer("[");
        StringBuffer strSubAll = new StringBuffer();
        JSONArray jsonArrayAll = JSONUtil.parseArray(configJson);
        for (int i = 0; i < jsonArrayAll.size(); i++) {
            if (!JSONUtil.isTypeJSON(configJson) || !JSONUtil.isTypeJSONArray(configJson)) {
                throw new AppException("==========过滤子json格式异常!==========");
            }
            if (null == jsonArrayAll.get(i)) {
                continue;
            }
            StringBuffer strSub = new StringBuffer();
            JSONArray jsonArraySub = JSONUtil.parseArray(jsonArrayAll.get(i));
            for (int j = 0; j < jsonArraySub.size(); j++) {
                if (null == jsonArraySub.get(j)) {
                    continue;
                }
                JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArraySub.get(j)));
                if (null == jsonObject || null == jsonObject.getString("field") || jsonObject.getString("field").contains(filtStr)) {
                    continue;
                }
                try {
                    if (null != jsonObject && jsonObject.getString("bg").contains("#595FE0")) {
                        Map<String, String> map = new HashMap<String, String>();
                        for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
                            if ("d".equals(entry.getKey())) {
                                map.put(entry.getKey(), formatDate(String.valueOf(new Date())));
                            } else {
                                map.put(entry.getKey(), entry.getValue().toString());
                            }
                        }
                        strSub.append(JSONUtil.toJsonStr(map)).append(",");
                        continue;
                    }
                } catch (Exception ex) {
                    //log.error("异常报文： {}", jsonObject.toJSONString());
                }
                strSub.append(jsonObject).append(",");
            }
            if (StringUtils.isNotEmpty(strSub.toString())) {
                strSubAll.append("[").append(strSub.substring(0, strSub.length() - 1)).append("]").append(",");
            }
        }
        strAll.append(strSubAll.substring(0, strSubAll.length() - 1)).append("]");
        return JSONUtil.toJsonStr(strAll);
    }

    /**
     * 检查数据管理配置表字段是否最新，不是则进行更新，无改类型数据则插入一条记录
     * 来自：sqfw_template_config表 config_json字段
     *
     * @param type
     * @param map
     */
    public static void checkDataManageConfigField(String type, String subType, LinkedHashMap<String, String> dictFieldAll, LinkedHashMap<String, String> map) {
        DataMangeConfigService dataMangeConfigService = (DataMangeConfigService) NutzIocUtils.getBeanByType(DataMangeConfigService.class);
        // 1.如果没有动态自定义字段，清空动态字段后返回直接返回
        if (ObjectUtils.isEmpty(map)) {
            dataMangeConfigService.update(Chain.make("view_fields_json", ""), Cnd.where("type", "LIKE", "%" + type + "%").and("sub_type", "=", subType).and(Sqfw_datamange_config::getDelFlag, "=", false));
            return;
        }
        // 2.如果有动态自定义字段，则更新配置文件
        try {
            Sqfw_datamange_config sqfwDatamangeConfig = dataMangeConfigService.fetch(
                    Cnd.where("type", "LIKE", "%" + type + "%").and("sub_type", "=", subType).and(Sqfw_datamange_config::getDelFlag, "=", false));
            if (null == sqfwDatamangeConfig) {
                log.error("data_config参数不存在");
                return;
            }
            // 3.如果map中有动态字段，则更新改类型对应记录
            Sqfw_datamange_config sqfwDatamangeConfigUpdate = new Sqfw_datamange_config();
            BeanUtil.copyProperties(sqfwDatamangeConfig, sqfwDatamangeConfigUpdate);
            sqfwDatamangeConfigUpdate.setUpdateDate(new Date());
            // .setUpdateBy(SecurityUtil.getUserId());
            StringBuffer sbFields = new StringBuffer();
            String fieldsAllStr = JSONUtil.toJsonStr(dictFieldAll);
            String viewFieldSource = sqfwDatamangeConfigUpdate.getViewFieldSource();
            for (String key : map.keySet()) {
                // 检查是否包含自定义字段
                if (fieldsAllStr.contains(key) && (!key.contains("custom_"))) {
                    continue;
                }
                dictFieldAll.put(key, String.valueOf(map.get(key)));
                if (null != viewFieldSource && !viewFieldSource.contains(key)) {
                    sbFields.append(",").append(key);
                }
            }
            log.info("==checkDataManageConfigField====custom_fields-size-{}", dictFieldAll.size());
            if (StringUtils.isNotEmpty(sbFields)) {
                log.info("==checkDataManageConfigField====custom_fields--{}", JSONUtil.toJsonStr(sbFields));
                sqfwDatamangeConfigUpdate.setViewFields(viewFieldSource + sbFields);
                sqfwDatamangeConfigUpdate.setViewFieldsJson(JSONUtil.toJsonStr(map));
                sqfwDatamangeConfigUpdate.setColNum(String.valueOf(dictFieldAll.size()));
                dataMangeConfigService.updateIgnoreNull(sqfwDatamangeConfigUpdate);
            }
        } catch (Exception ex) {
            log.error("checkDataManageConfigField--{}", ex.getStackTrace());
        }
    }

    /**
     * 检查数据管理配置表字段更新至全量字段中
     */
    public static void checkDataManageConfigPutFieldAll(LinkedHashMap<String, String> dictFieldAll) {
        // 1.查询出所有的动态字段，拼接入全量字段map中
        try {
            DataMangeConfigService dataMangeConfigService = (DataMangeConfigService) NutzIocUtils.getBeanByType(DataMangeConfigService.class);
            List<Sqfw_datamange_config> sqfwDatamangeConfigs = dataMangeConfigService.query(Cnd.where(Sqfw_datamange_config::getDelFlag, "=", false).and("sub_type", "=", "all"));
            if (null == sqfwDatamangeConfigs) {
                log.error("data_config参数不存在");
                return;
            }
            Map<String, String> dynamicFieldMap = new HashMap<String, String>();
            for (Sqfw_datamange_config sqfwDatamangeConfig : sqfwDatamangeConfigs) {
                if (!JSONUtil.isTypeJSON(sqfwDatamangeConfig.getViewFieldsJson())) {
                    continue;
                }
                JSONObject jsonObject = JSONObject.parseObject(String.valueOf(sqfwDatamangeConfig.getViewFieldsJson()));
                for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
                    dynamicFieldMap.put(entry.getKey(), entry.getValue().toString());
                }
            }
            // 3.如果map中有动态字段，则更新改类型对应记录
            Sqfw_datamange_config sqfwDatamangeConfigUpdate = new Sqfw_datamange_config();
            StringBuffer sbFields = new StringBuffer();
            String fieldsAllStr = JSONUtil.toJsonStr(dictFieldAll);
            String viewFieldSource = sqfwDatamangeConfigUpdate.getViewFieldSource();
            for (String key : dynamicFieldMap.keySet()) {
                // 检查是否包含自定义字段
                if (fieldsAllStr.contains(key) && (!key.contains("custom_"))) {
                    continue;
                }
                dictFieldAll.put(key, String.valueOf(dynamicFieldMap.get(key)));
                if (null != viewFieldSource && !viewFieldSource.contains(key)) {
                    sbFields.append(",'").append(key).append("'");
                }
            }
            log.info("==checkDataManageConfigField====custom_fields-size-{}", dictFieldAll.size());
        } catch (Exception ex) {
            log.error("checkDataManageConfigField--{}", ex.getStackTrace());
        }
    }

    /**
     * 检查字段在数据中的下标位置
     *
     * @param excelData
     * @param fieldName
     * @return
     */
    public static int searchJsonArrayForIndex(String excelData, String fieldName) {
        if (!JSONUtil.isTypeJSONArray(String.valueOf(excelData))) {
            return -1;
        }
        JSONArray jsonArray = JSONUtil.parseArray(excelData);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            if (jsonObject.getString("field").equals(fieldName)) {
                return i;
            }
        }
        return -1; // Return -1 if the field is not found
    }

    /**
     * 查询数据是否存在
     *
     * @return
     */
    public static int countExcelData(String tableName, String cond) {
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        StringBuffer sb = new StringBuffer("SELECT count(1) FROM ");
        sb.append(tableName).append(cond);
        Sql sql = Sqls.create(sb.toString());
        sql.setCallback(Sqls.callback.integer());
        dao.execute(sql);
        return sql.getInt();
    }

    /**
     * 更新数据
     *
     * @return
     */
    public static void updateExcelData(String sqlStr) {
        Trans.exec(() -> {
            Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
            Sql sql = Sqls.create(sqlStr);
            sql.setCallback(Sqls.callback.integer());
            dao.execute(sql);
        });
        try {
            Trans.commit();
        } catch (Exception e) {
            log.error("事务提交异常，{}", sqlStr);
        }
    }

    /**
     * 解析json数组，返回解析结果
     *
     * @param excelDataJsonStr
     */
    public static String parseExcelDataForParamdata(String excelDataJsonStr) {
        String resStr = "";
        if (!JSONUtil.isTypeJSONArray(excelDataJsonStr)) {
            return resStr;
        }
        Map<String, String> resMap = new HashMap<String, String>();
        JSONArray jsonArray = JSONUtil.parseArray(excelDataJsonStr);
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray.get(i)));
            String field = jsonObject.getString("field");
            String value = jsonObject.getString("v");
            if (StringUtils.isNotEmpty(field) && StringUtils.isNotEmpty(value)) {
                resMap.put(field, value);
            }
        }
        return JSONUtil.toJsonStr(resMap);
    }

    /**
     * 获取bean中所有值的拼接
     *
     * @param bean
     */
    public static String getFieldValuesConcatenated(Object bean) {
        if (bean == null) {
            return null;
        }
        StringBuilder sb = new StringBuilder();
        try {
            Class<?> clazz = bean.getClass();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                Object obj = field.get(bean);
                if (null == obj) {
                    continue;
                }
                String value = obj.toString();
                if (StringUtils.isNotEmpty(value)) {
                    sb.append(value);
                }
            }
            // 获取父类信息
            Class<?> superClazz = clazz.getSuperclass();
            Field[] superFields = superClazz.getDeclaredFields();
            for (Field field : superFields) {
                field.setAccessible(true);
                Object obj = field.get(bean);
                if (null == obj) {
                    continue;
                }
                String value = obj.toString();
                if (StringUtils.isNotEmpty(value)) {
                    sb.append(value);
                }
            }
        } catch (Exception ex) {
            log.error("获取bean中所有值的拼接异常，{}", ex.getMessage());
        }
        log.info("redis_name--{}", sb.toString());
        return sb.toString();
    }

    /**
     * 将字符串转成数字串
     *
     * @param str
     */
    public static String StringToNumbers(String str) {
        if (StringUtils.isEmpty(str)) {
            return "0";
        }
        StringBuilder numberSequence = new StringBuilder();
        try {
            for (char c : str.toCharArray()) {
                numberSequence.append((int) c);
            }
        } catch (Exception ex) {
            log.error("将字符串转成数字串异常，{}", ex.getMessage());
        }
        return numberSequence.toString();
    }

    /**
     * 查询用户名称
     *
     * @return
     */
    private static String queryUserNameByIds(List<String> ids) {
        if (ids.size() <= 0) {
            return "";
        }
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        StringBuffer sb = new StringBuffer("SELECT username FROM shequ.\"sys_user\" WHERE id IN (@ids)");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("ids", ids);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                StringBuffer sb = new StringBuffer();
                while (rs.next()) {
                    sb.append(rs.getString("username")).append(",");
                }
                return sb.toString().substring(0, sb.length() - 1);
            }
        });
        dao.execute(sql);
        return sql.getString();
    }
}

